﻿
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_validate_flow_jump_rule]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[fn_validate_flow_jump_rule];
GO
CREATE FUNCTION [dbo].[fn_validate_flow_jump_rule]
(
    @docId int,             
    @fieldName nvarchar(50),    -- The field to be evaluated by the jump rule
    @staffCaste int,           -- The caste (职级) of the staff
    @valueToCompare int,
    @op nvarchar(10)
)
returns bit
as
begin
    IF @fieldName = 'caste'
    BEGIN
        IF @staffCaste IS NULL
            RETURN 0;

        RETURN dbo.fn_compare_int(@staffCaste, @valueToCompare, @op);
    END

    -- Get the value of the field for the given document (UDS_Flow_Style_Data)
    DECLARE @value nvarchar(50);
    SELECT @value = 
            CASE @fieldName
                WHEN 'A' THEN A
                WHEN 'B' THEN B
                WHEN 'C' THEN C
                WHEN 'D' THEN D
                WHEN 'E' THEN E
                WHEN 'F' THEN F
                WHEN 'G' THEN G
                WHEN 'H' THEN H
                WHEN 'I' THEN I
                WHEN 'J' THEN J
                WHEN 'K' THEN K
                WHEN 'L' THEN L
                WHEN 'M' THEN M
                WHEN 'N' THEN N
                WHEN 'O' THEN O
                WHEN 'P' THEN P
                WHEN 'Q' THEN Q
                WHEN 'R' THEN R
                WHEN 'S' THEN S
                WHEN 'T' THEN T
                WHEN 'U' THEN U
                WHEN 'V' THEN V
                WHEN 'W' THEN W
                WHEN 'X' THEN X
                WHEN 'Y' THEN Y
                ELSE Z
            END
        FROM dbo.UDS_Flow_Style_Data 
        WHERE Doc_ID = @docId;

    IF @@ROWCOUNT = 0
        RETURN 0;       -- no data found

    RETURN dbo.fn_compare_int(cast(@value as int), @valueToCompare, @op);
end
GO